﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Models
{
    public class CommentModel
    {
        private string connectionString;
        private SqlConnection connect;

        public CommentModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public string[][] getCommentbyID(int ID, int page, int offset)
        {
            string[][] result = new string[5][];
            UsersModel tbUsers = new UsersModel();
            try
            {
                connect.Open();
                string selectSQL = "WITH LIMIT AS(SELECT *,ROW_NUMBER() OVER(ORDER BY created_date DESC) AS rowNum FROM Comment WHERE book_id = " + ID.ToString() + ")";
                selectSQL += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * offset + 1) + " AND " + (page * offset);
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[3];
                    result[i][0] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["created_date"].ToString())).ToString(" HH:mm:ss - dd/MM/yyyy");
                    result[i][1] = reader["description"].ToString();
                    result[i][2] = tbUsers.getUserNamebyID(Convert.ToInt32(reader["user_id"]));
                    i++;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int countCommentbyID(int ID)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total_comment FROM Comment WHERE book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_comment"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int insertComment(int userID, int bookID, string comment)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "INSERT INTO Comment (user_id,book_id,description,created_date) VALUES (N'" + userID.ToString() + "','" + bookID.ToString() + "',N'" + comment + "'," + Utils.Now() + ")";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
    }
}
